package com.zr.daoImpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;

import com.zr.dao.HRDaodtp;
import com.zr.model.Employee;
import com.zr.model.Leave;
import com.zr.model.PersonData;
import com.zr.model.ResignDtp;
import com.zr.util.JDBCUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

public class HRDaoImpldtp implements HRDaodtp{
	//注册员工
	@Override
	public int addUser(String ename,int eage, String esex, String enation, String eidcard, String eaddress,
			String eeducation, String eintime, String eposition, String eremark, int did,String ephone) {
	StringBuffer sql=new StringBuffer();
	int eid=0;
	sql.append(" insert into employee(epsw,ename,eage,esex,eidcard,");
	sql.append(" enation,eaddress,eeducation,eintime,did,eposition,eremark,ephone)");
	sql.append(" values (?,?,?,?,?,?,?,?,?,?,?,?,?)");
	Connection con=JDBCUtil.getConnection();
	try {
		PreparedStatement ps=con.prepareStatement(sql.toString());
		ps.setString(1, "123456");
		ps.setString(2, ename);
		ps.setInt(3, eage);
		ps.setString(4, esex);
		ps.setString(5, eidcard);
		ps.setString(6, enation);
		ps.setString(7, eaddress);
		ps.setString(8, eeducation);
		ps.setString(9, eintime);
		ps.setInt(10, did);
		ps.setString(11, eposition);
		ps.setString(12, eremark);
		ps.setString(13, ephone);
		int i=ps.executeUpdate();
		if(i>0){
			StringBuffer sql2=new StringBuffer();
			sql2.append(" select employee.eid");
			sql2.append(" from employee order by eid desc limit 0,1");
			Connection conn=JDBCUtil.getConnection();
			PreparedStatement pss=conn.prepareStatement(sql2.toString());
			ResultSet rss=pss.executeQuery();
			if(rss.next()){
				eid=rss.getInt("eid");
			}
		}
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		e.printStackTrace();
	}
	
		return eid;
	}
	//部门下拉选
	@Override
	public List<String> getSelectDid() {
		List<String> li=new ArrayList<String>();
		StringBuffer sql=new StringBuffer();
		sql.append(" select department.did,department.dname,department.dmanager_id");
		sql.append(" from department");
		Connection  con   =  JDBCUtil.getConnection();
		try {
			PreparedStatement   ps =  con.prepareStatement(sql.toString());
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				String str=rs.getString("dname");
				li.add(str);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return li;
		
	}
	//人事变动（+搜索）count
	@Override
	public int getEmplsCount(String name) {
		int count=0;
		StringBuffer sql=new StringBuffer();
		sql.append(" select count(eid) as count1");
		sql.append(" from employee");
		if(StringUtils.isNotBlank(name)){
			sql.append(" where ename like ?");
		}	
		Connection  con   =  JDBCUtil.getConnection();
		try {
			PreparedStatement   ps =  con.prepareStatement(sql.toString());
			if(StringUtils.isNotBlank(name)){
				ps.setString(1, "%"+name+"%");
			}	
			ResultSet rs=ps.executeQuery();		
			if(rs.next()){
				count=rs.getInt("count1");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}
	//人事变动（+搜索）
	@Override
	public List<Employee> getEmpls(int page, int rows,String name) {
		List<Employee> li=new ArrayList<Employee>();
		StringBuffer sql=new StringBuffer();
		sql.append(" select employee.eid,employee.ename,department.dname,employee.eposition,employee.eremark");
		sql.append(" from employee employee");
		sql.append(" INNER JOIN department");
		sql.append(" on department.did=employee.did ");
		if(StringUtils.isNotBlank(name)){
			sql.append(" where employee.ename like ?");
		}
		
		sql.append(" limit ?,? ");
		
		Connection  con   =  JDBCUtil.getConnection();
		try {
			PreparedStatement   ps =  con.prepareStatement(sql.toString());
			if(StringUtils.isNotBlank(name)){
				ps.setString(1,"%"+name+"%");
				ps.setInt(2, (page-1)*rows);
				ps.setInt(3, rows);
			}else{
				ps.setInt(1, (page-1)*rows);
				ps.setInt(2, rows);
			}
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				Employee e=new Employee();
				e.setEid(rs.getInt("eid"));
				e.setEname(rs.getString("ename"));
				e.setDepartment(rs.getString("dname"));
				e.setEposition(rs.getString("eposition"));
				e.setRemake(rs.getString("eremark"));
				li.add(e);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return li;
	}
	//人事变动（部门+职位）
	@Override
	public boolean ChangeJob(int eid,String departement, String position) {
		boolean flag=false;
		StringBuffer sql=new StringBuffer();
		sql.append(" update employee set employee.eposition=? ,");
		sql.append(" employee.did=(select department.did from department WHERE department.dname=?)");
		sql.append(" WHERE employee.eid=?");
		Connection  con   =  JDBCUtil.getConnection();
		try {
			PreparedStatement   ps =  con.prepareStatement(sql.toString());
			ps.setString(1, position);
			ps.setString(2, departement);
			ps.setInt(3, eid);
			int i=ps.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}
	//获取请假员工总数
	@Override
	public int getLeavesCount(String state) {
		int count=0;
		StringBuffer sql=new StringBuffer();
		sql.append(" select count(vacation.id) as counts");
		sql.append(" from vacation");
		if(StringUtils.isNotBlank(state)){
			sql.append(" where vacation.state=?");			
		}
		Connection  con   =  JDBCUtil.getConnection();
		try {
			PreparedStatement   ps =  con.prepareStatement(sql.toString());
			if(StringUtils.isNotBlank(state)){
				ps.setString(1, state);				
			}
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				count=rs.getInt("counts");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return count;
	}
	//请假
	@Override
	public List<Leave> getleaves(int page, int rows,String state) {
		List<Leave> li=new ArrayList<Leave>();
		StringBuffer sql=new StringBuffer();
		sql.append(" select employee.eid,employee.ename,department.dname,vacation.edata,vacation.id,vacation.retime,");
		sql.append(" vacation.etartday,vacation.actualday,vacation.category,vacation.reason,vacation.state,vacation.remake");
		sql.append(" from employee");
		sql.append(" inner join vacation");
		sql.append(" on employee.eid=vacation.eid");
		sql.append(" inner join department");
		sql.append(" on department.did=employee.did");
		if(StringUtils.isNotBlank(state)){
			sql.append(" where vacation.state=?");
		}
		sql.append(" limit ?,?");
		Connection  con   =  JDBCUtil.getConnection();
		try {
			PreparedStatement   ps =  con.prepareStatement(sql.toString());
			if(StringUtils.isNotBlank(state)){
				ps.setString(1, state);
				ps.setInt(2, (page-1)*rows);
				ps.setInt(3, rows);
			}else{
				ps.setInt(1, (page-1)*rows);
				ps.setInt(2, rows);			
			}
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				Leave le=new Leave();
				le.setId(rs.getInt("id"));
				le.setEid(rs.getInt("eid"));
				le.setEname(rs.getString("ename"));
				le.setDname(rs.getString("dname"));
				le.setEdata(rs.getString("edata"));
				le.setEtartday(rs.getString("etartday"));
				le.setActualday(rs.getString("actualday"));
				le.setCategory(rs.getString("category"));
				le.setReason(rs.getString("reason"));
				le.setState(rs.getString("state"));
				le.setRetime(rs.getString("retime"));
				le.setRemake(rs.getString("remake"));
				li.add(le);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return li;
	}

	@Override
	public boolean delEmpl(int eid) {
		boolean flag=false;
		StringBuffer sql=new StringBuffer();
		sql.append(" delete from employee");
		sql.append(" where employee.eid=?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());
			ps.setInt(1, eid);
			int i=ps.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return flag;
	}

	@Override
	public Leave getListById(int id) {
		Leave le=new Leave();
		StringBuffer sql=new StringBuffer();
		sql.append(" select employee.eid,employee.ename,department.dname,vacation.edata,");
		sql.append(" vacation.etartday,vacation.actualday,vacation.category,vacation.id,");
		sql.append(" vacation.reason,vacation.state,vacation.remake,vacation.retime");
		sql.append(" from employee");
		sql.append(" inner join vacation ");
		sql.append(" on employee.eid=vacation.eid");
		sql.append(" inner join department");
		sql.append(" on department.did=employee.did");
		sql.append(" where vacation.id=?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());
			ps.setInt(1, id);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				le.setEid(rs.getInt("eid"));
				le.setId(rs.getInt("id"));
				le.setEname(rs.getString("ename"));
				le.setDname(rs.getString("dname"));
				le.setEdata(rs.getString("edata"));
				le.setEtartday(rs.getString("etartday"));
				le.setActualday(rs.getString("actualday"));
				le.setCategory(rs.getString("category"));
				le.setReason(rs.getString("reason"));
				le.setState(rs.getString("state"));
				le.setRemake(rs.getString("remake"));
				le.setRetime(rs.getString("retime"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return le;
	}
	@Override
	public boolean subVacation(int id, String opinion, String retime, String remake) {
		// TODO Auto-generated method stub
		boolean flag=false;
		StringBuffer sql=new StringBuffer();
		sql.append(" update vacation set vacation.opinion=?,vacation.retime=?,vacation.remake=?");
		sql.append(" where vacation.id=?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());
			ps.setString(1, opinion);
			ps.setString(2, retime);
			ps.setString(3, remake);
			ps.setInt(4, id);
			int i=ps.executeUpdate();
			if(i>0){
				StringBuffer sq=new StringBuffer();
				sq.append(" update vacation set vacation.state='已审核'");
				sq.append(" where vacation.id=?");
				Connection conn=JDBCUtil.getConnection();
				PreparedStatement prs=conn.prepareStatement(sq.toString());
				prs.setInt(1, id);
				int j=prs.executeUpdate();
				if(j>0){
					flag=true;
				}
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}
	@Override//ygrz
	public int findAllCount(String name) {
		int count=0;
		StringBuffer sql=new StringBuffer();
		sql.append(" select count(employee.eid) as counts");
		sql.append(" from employee");
		if(StringUtils.isNotBlank(name)){
			sql.append(" where employee.ename like ?");
		}
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());
			if(StringUtils.isNotBlank(name)){
				ps.setString(1, "%"+name+"%");
			}
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				count=rs.getInt("counts");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return count;
	}

	@Override
	public List<PersonData> getAll(int page, int rows,String name) {
		List<PersonData> li=new ArrayList<PersonData>();
		StringBuffer sql=new StringBuffer();
		sql.append(" select employee.eid,employee.ename,employee.eage,employee.esex,employee.ephone,");
		sql.append(" employee.eidcard,employee.enation,employee.eaddress,employee.eeducation,");
		sql.append(" employee.eintime,department.dname,employee.eposition");
		sql.append(" from employee");
		sql.append(" inner join department");
		sql.append(" on department.did=employee.did");
		if(StringUtils.isNotBlank(name)){
			sql.append(" where employee.ename like ?");
		}
		sql.append(" limit ?,?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());
			if(StringUtils.isNotBlank(name)){
				ps.setString(1, "%"+name+"%");
				ps.setInt(2, (page-1)*rows);
				ps.setInt(3, rows);
			}else{
				ps.setInt(1, (page-1)*rows);
				ps.setInt(2, rows);
			}
			
			
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				PersonData p=new PersonData();
				p.setEid(rs.getInt("eid"));
				p.setEname(rs.getString("ename"));
				p.setEage(rs.getInt("eage"));
				p.setEsex(rs.getString("esex"));
				p.setEidcard(rs.getString("eidcard"));
				p.setEnation(rs.getString("enation"));
				p.setEaddress(rs.getString("eaddress"));
				p.setEeducation(rs.getString("eeducation"));
				p.setEintime(rs.getString("eintime"));
				p.setDname(rs.getString("dname"));
				p.setEposition(rs.getString("eposition"));
				p.setEphone(rs.getString("ephone"));
				li.add(p);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return li;
	}
	@Override
	public boolean editEmpl(int eid, String mname, int mage, String meducation, String mdizi, String mphone) {
		// TODO Auto-generated method stub
		boolean flag=false;
		StringBuffer sql=new StringBuffer();
		sql.append(" update employee set employee.ename=?,employee.eage=?,");
		sql.append(" employee.eaddress=?,employee.eeducation=?,employee.ephone=?");
		sql.append(" where employee.eid=?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());
			ps.setString(1, mname);
			ps.setInt(2, mage);
			ps.setString(3, mdizi);
			ps.setString(4, meducation);
			ps.setString(5, mphone);
			ps.setInt(6, eid);
			int i=ps.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return flag;
	}
	@Override
	public int resignCount(String state) {
		int count=0;
		StringBuffer sql=new StringBuffer();
		sql.append(" select count(resign.id) as scount");
		sql.append(" from resign");
		if(StringUtils.isNotBlank(state)){
			sql.append(" where resign.state=?");
		}
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());
			if(StringUtils.isNotBlank(state)){
				ps.setString(1, state);
			}
			ResultSet rs=ps.executeQuery();
			if(rs.next()){
				count=rs.getInt("scount");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}	
		return count;
	}
	@Override
	public List<ResignDtp> getResign(String state, int page, int rows) {
		List<ResignDtp> li=new ArrayList<ResignDtp>();
		StringBuffer sql=new StringBuffer();
		sql.append("  select employee.ename,employee.eposition,employee.eintime,");
		sql.append(" resign.id,resign.date,resign.reason,resign.ltime,resign.opinion,resign.eid,");
		sql.append(" resign.retime,department.dname,resign.state");
		sql.append(" from resign");
		sql.append(" inner join employee");
		sql.append(" on employee.eid=resign.eid");
		sql.append(" inner join department");
		sql.append(" on department.did=employee.did");
		if(StringUtils.isNotBlank(state)){
			sql.append(" where resign.state=?");
		}
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());
			if(StringUtils.isNotBlank(state)){
				ps.setString(1, state);
			}
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				ResignDtp res=new ResignDtp();
				res.setEname(rs.getString("ename"));
				res.setEposition(rs.getString("eposition"));
				res.setEintime(rs.getString("eintime"));
				res.setId(rs.getInt("id"));
				res.setDate(rs.getString("date"));
				res.setReason(rs.getString("reason"));
				res.setLtime(rs.getString("ltime"));
				res.setOpinion(rs.getString("opinion"));
				res.setEid(rs.getInt("eid"));
				res.setRetime(rs.getString("retime"));
				res.setDname(rs.getString("dname"));
				res.setState(rs.getString("state"));
				li.add(res);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return li;
	}
	//通过离职单号获取离职信息
	@Override
	public ResignDtp getResignData(int id) {
		// TODO Auto-generated method stub
		ResignDtp rd=new ResignDtp();
		StringBuffer sql=new StringBuffer();
		sql.append("  select employee.ename,employee.eposition,employee.eintime,");
		sql.append(" resign.id,resign.date,resign.reason,resign.ltime,resign.opinion,resign.eid,");
		sql.append(" resign.retime,department.dname,resign.state,resign.remark");
		sql.append(" from resign");
		sql.append(" inner join employee");
		sql.append(" on employee.eid=resign.eid");
		sql.append(" inner join department");
		sql.append(" on department.did=employee.did");
		sql.append(" where resign.id=?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());
			ps.setInt(1, id);
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				rd.setEname(rs.getString("ename"));
				rd.setEposition(rs.getString("eposition"));
				rd.setEintime(rs.getString("eintime"));
				rd.setId(rs.getInt("id"));
				rd.setDate(rs.getString("date"));
				rd.setReason(rs.getString("reason"));
				rd.setLtime(rs.getString("ltime"));
				rd.setOpinion(rs.getString("opinion"));
				rd.setEid(rs.getInt("eid"));
				rd.setRetime(rs.getString("retime"));
				rd.setDname(rs.getString("dname"));
				rd.setState(rs.getString("state"));
				rd.setRemark(rs.getString("remark"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return rd;
	}
	@Override
	public boolean checkResign(int id, String opinion, String retime, String remark) {
		// TODO Auto-generated method stub
		boolean flag=false;
		StringBuffer sql=new StringBuffer();
		sql.append(" update resign set resign.opinion=?,resign.retime=?,");
		sql.append(" resign.remark=?,resign.state='已审核'");
		sql.append(" where resign.id=?");
		Connection con=JDBCUtil.getConnection();
		try {
			PreparedStatement ps=con.prepareStatement(sql.toString());
			ps.setString(1, opinion);
			ps.setString(2, retime);
			ps.setString(3, remark);
			ps.setInt(4, id);
			int i=ps.executeUpdate();
			if(i>0){
				flag=true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return flag;
	}

}
